#!/usr/bin/env node

const Database = require('better-sqlite3');
const db = new Database('./data/delivery-management.db');

console.log('🔍 验证实例表结构和存储方式...\n');

// 1. 检查所有实例表
const instanceTables = db.prepare(`
  SELECT name FROM sqlite_master 
  WHERE type='table' AND name LIKE 'device_instances_%'
  ORDER BY name
`).all();

console.log(`1. 当前实例表数量: ${instanceTables.length}`);
instanceTables.forEach(table => {
  console.log(`   - ${table.name}`);
});

// 2. 分析实例表命名规则和对应关系
console.log('\n2. 实例表命名分析:');
const mappings = db.prepare(`
  SELECT DISTINCT specialty, classification_name, english_name, classification_code
  FROM device_type_mappings 
  ORDER BY specialty, classification_name
`).all();

const tableByObjectClass = {};
mappings.forEach(mapping => {
  const specialtyEn = mapping.specialty === '管道专业' ? 'piping' : 
    mapping.specialty === '电气专业' ? 'electrical' : 
    mapping.specialty === '暖通专业' ? 'hvac' : 
    mapping.specialty === '通信专业' ? 'communication' : 'instrumentation';
  
  const expectedTableName = `device_instances_${specialtyEn}_${mapping.english_name}`;
  
  if (!tableByObjectClass[mapping.classification_name]) {
    tableByObjectClass[mapping.classification_name] = [];
  }
  tableByObjectClass[mapping.classification_name].push({
    specialty: mapping.specialty,
    table: expectedTableName,
    code: mapping.classification_code
  });
});

console.log('\n按对象类分组的实例表:');
Object.keys(tableByObjectClass).forEach(className => {
  const tables = tableByObjectClass[className];
  console.log(`\n对象类: ${className}`);
  tables.forEach(t => {
    console.log(`  专业: ${t.specialty} -> 表: ${t.table}`);
  });
});

// 3. 检查具体的实例表内容结构
console.log('\n3. 检查实例表字段结构 (以仪表专业为例):');
const sampleTable = instanceTables.find(t => t.name.includes('instrumentation'));
if (sampleTable) {
  console.log(`\n检查表: ${sampleTable.name}`);
  const columns = db.prepare(`PRAGMA table_info(${sampleTable.name})`).all();
  console.log('字段列表:');
  columns.forEach(col => {
    console.log(`  - ${col.name} (${col.type})${col.pk ? ' [主键]' : ''}${col.notnull ? ' [非空]' : ''}`);
  });
  
  // 检查是否有设备类型标识字段
  const hasDeviceTypeField = columns.some(col => 
    col.name.includes('device_type') || col.name.includes('device_id') || col.name.includes('classification')
  );
  console.log(`\n包含设备类型标识字段: ${hasDeviceTypeField}`);
}

// 4. 验证同一对象类下的不同设备类型
console.log('\n4. 验证同一对象类的设备类型分组:');
const devicesByClass = db.prepare(`
  SELECT classification_name, COUNT(DISTINCT device_id) as device_count,
         GROUP_CONCAT(DISTINCT device_id) as device_list
  FROM devices 
  WHERE classification_name IS NOT NULL 
  GROUP BY classification_name
  HAVING device_count > 1
  ORDER BY device_count DESC
`).all();

console.log(`\n有多个设备类型的对象类 (${devicesByClass.length}个):`);
devicesByClass.forEach(cls => {
  console.log(`\n对象类: ${cls.classification_name}`);
  console.log(`  设备数量: ${cls.device_count}`);
  console.log(`  设备列表: ${cls.device_list}`);
  
  // 检查对应的实例表
  const mapping = db.prepare(`
    SELECT english_name, specialty FROM device_type_mappings 
    WHERE classification_name = ? LIMIT 1
  `).get(cls.classification_name);
  
  if (mapping) {
    const specialtyEn = mapping.specialty === '仪表专业' ? 'instrumentation' : 
                      mapping.specialty === '电气专业' ? 'electrical' : 
                      mapping.specialty === '通信专业' ? 'communication' : 
                      mapping.specialty === '管道专业' ? 'piping' : 'hvac';
    const expectedTable = `device_instances_${specialtyEn}_${mapping.english_name}`;
    console.log(`  对应实例表: ${expectedTable}`);
    
    // 检查表是否存在
    const tableExists = instanceTables.some(t => t.name === expectedTable);
    console.log(`  表是否存在: ${tableExists}`);
  }
});

// 5. 检查实例表中设备类型字段的使用情况
console.log('\n5. 检查实例表中设备类型标识:');
if (sampleTable) {
  console.log(`\n分析表: ${sampleTable.name}`);
  
  // 查看表中是否有数据
  const rowCount = db.prepare(`SELECT COUNT(*) as count FROM ${sampleTable.name}`).get();
  console.log(`  数据行数: ${rowCount.count}`);
  
  if (rowCount.count > 0) {
    // 查看前几行数据的结构
    const sampleData = db.prepare(`SELECT * FROM ${sampleTable.name} LIMIT 3`).all();
    console.log('\n  样例数据:');
    sampleData.forEach((row, index) => {
      console.log(`    行 ${index + 1}:`);
      Object.keys(row).forEach(key => {
        if (key.includes('device') || key.includes('type') || key.includes('id')) {
          console.log(`      ${key}: ${row[key]}`);
        }
      });
    });
  }
}

db.close();